package com.b2c.repository.oms;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.b2c.entity.result.PagingResponse;
import com.b2c.common.utils.DateUtil;
import com.b2c.entity.DataRow;
import com.b2c.entity.ErpOrderEntity;
import com.b2c.entity.ErpOrderItemEntity;
import com.b2c.entity.ErpOrderReturnEntity;
import com.b2c.entity.douyin.*;
import com.b2c.entity.erp.ErpGoodsSpecEntity;
import com.b2c.entity.erp.enums.ErpOrderSourceEnum;
import com.b2c.entity.result.EnumResultVo;
import com.b2c.entity.result.ResultVo;
import com.b2c.entity.enums.EnumErpOrderSendStatus;
import com.b2c.entity.enums.erp.EnumOrderReturnStatus;
import com.b2c.entity.enums.third.EnumDouYinOrderRefundStatus;
import com.b2c.entity.enums.third.EnumDouYinOrderStatus;
import com.b2c.repository.Tables;
import com.b2c.entity.vo.DyOrderImportOrderVo;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.interceptor.TransactionAspectSupport;
import org.springframework.util.StringUtils;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 抖音
 */
@Repository
public class DouyinOrderRepository {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    private static Logger log = LoggerFactory.getLogger(DouyinOrderRepository.class);

    protected int getTotalSize() {
        return jdbcTemplate.queryForObject("SELECT FOUND_ROWS() as row_num;", int.class);
    }

    @Transactional(rollbackFor = Exception.class)
    public ResultVo<Integer> editDouYinOrder(DcDouyinOrdersEntity order, Integer flag) {
        Long countId = jdbcTemplate.queryForObject(
                "SELECT IFNULL((select id from dc_douyin_orders where order_id=? limit 1),0) id ", Long.class,
                order.getOrderId());
        if (countId > 0) {
            // log.info("本地订单存在，"+order.getOrderId()+"最新状态："+order.getOrderStatus());
            // var o = jdbcTemplate.queryForObject("SELECT * FROM dc_douyin_orders where
            // order_id=? limit 1",new
            // BeanPropertyRowMapper<>(DcDouyinOrdersEntity.class),order.getOrderId());
            // if(o.getOrderStatus().intValue() != 3)
            // log.info("本地订单："+o.getOrderId()+"，状态:"+o.getOrderStatus());

            // String sql = "SELECT COUNT(0) FROM erp_order where order_num=? ";
            // Long s = jdbcTemplate.queryForObject(sql, Long.class, order.getOrderId());
            // if(s.intValue()==0) log.info("本地订单存在，仓库系统没有找到订单："+order.getOrderId());

            // 订单状态1 待确认/待支付（订单创建完毕）105 已支付 2 备货中 101
            if (flag == 1) {
                if (order.getOrderStatus() == 2) {
                    jdbcTemplate.update(
                            "update dc_douyin_orders set province=?,city=?,town=?,street=?,encryptDetail=?,encrypt_post_receiver=?,encrypt_post_tel=?,phoneKey=?,addressKey=?  where order_id=?",
                            order.getProvince(), order.getCity(), order.getTown(), order.getStreet(),
                            order.getEncryptDetail(), order.getEncrypt_post_receiver(), order.getEncrypt_post_tel(),
                            order.getPhoneKey(), order.getAddressKey(), order.getOrderId());
                }
                jdbcTemplate.update(
                        "update dc_douyin_orders set order_status=?,logistics_company=?,logistics_code=? where order_id=?",
                        order.getOrderStatus(), order.getLogisticsCompany(), order.getLogisticsCode(),
                        order.getOrderId());
            }
            /*
             * for (var item:order.getSkuOrderList()) {
             * jdbcTemplate.
             * update("update dc_douyin_orders_items set author_id=?,author_name=? where  dc_douyin_orders_id=? and code=?"
             * ,item.getAuthorId(),item.getAuthorName(),countId,item.getCode());
             * }
             */
            log.info("拉取新订单，订单" + order.getOrderId() + "已存在");
            return new ResultVo<>(EnumResultVo.DataExist, "已存在");
        }
        // else if(1==1){
        // log.info("本地没有找到订单："+order.getOrderId());
        // return new ResultVo<>(EnumResultVo.DataExist, "已存在");
        // }

        /***** 1、添加order *****/
        StringBuilder orderInsertSQL = new StringBuilder();
        orderInsertSQL.append("INSERT INTO dc_douyin_orders");
        orderInsertSQL.append(" SET ");
        orderInsertSQL.append(" order_id=?,");
        orderInsertSQL.append(" shop_id=?,");
        orderInsertSQL.append(" user_name=?,");
        orderInsertSQL.append(" post_addr=?,");
        orderInsertSQL.append(" post_code=?,");
        orderInsertSQL.append(" post_receiver=?,");
        orderInsertSQL.append(" post_tel=?,");
        orderInsertSQL.append(" buyer_words=?,");
        orderInsertSQL.append(" seller_words=?,");
        orderInsertSQL.append(" logistics_id=?,");
        orderInsertSQL.append(" logistics_code=?,");
        orderInsertSQL.append(" logistics_time=?,");
        orderInsertSQL.append(" receipt_time=?,");
        orderInsertSQL.append(" order_status=?,");
        orderInsertSQL.append(" create_time=?,");
        orderInsertSQL.append(" exp_ship_time=?, ");
        orderInsertSQL.append(" update_time=?,");
        orderInsertSQL.append(" cancel_reason=?,");
        orderInsertSQL.append(" pay_type=?,");
        orderInsertSQL.append(" pay_time=?,");
        orderInsertSQL.append(" post_amount=?,");
        orderInsertSQL.append(" coupon_amount=?,");
        orderInsertSQL.append(" shop_coupon_amount=?,");
        orderInsertSQL.append(" coupon_info=?,");
        orderInsertSQL.append(" order_total_amount=?,");
        orderInsertSQL.append(" is_comment=?,");
        orderInsertSQL.append(" trade_type=?,");
        orderInsertSQL.append(" c_type=?,");
        orderInsertSQL.append(" b_type=?,");
        orderInsertSQL.append(" cos_ratio=?,");
        orderInsertSQL.append(" province=?,");
        orderInsertSQL.append(" city=?,");
        orderInsertSQL.append(" town=?,");
        orderInsertSQL.append(" street=?,");
        orderInsertSQL.append(" encryptDetail=?,");
        orderInsertSQL.append(" encrypt_post_tel=?,");
        orderInsertSQL.append(" encrypt_post_receiver=?,");
        orderInsertSQL.append(" phoneKey=?,");
        orderInsertSQL.append(" addressKey=?,");
        orderInsertSQL.append(" createOn=? ");
        try {
            KeyHolder keyHolder = new GeneratedKeyHolder();
            jdbcTemplate.update(new PreparedStatementCreator() {
                @Override
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    PreparedStatement ps = connection.prepareStatement(orderInsertSQL.toString(),
                            Statement.RETURN_GENERATED_KEYS);
                    ps.setString(1, order.getOrderId());
                    ps.setLong(2, order.getShopId());
                    ps.setString(3, order.getUserName());
                    ps.setString(4, "");// order.getPostAddr()
                    ps.setString(5, order.getPostCode());
                    ps.setString(6, order.getPostReceiver());
                    ps.setString(7, order.getPostTel());
                    ps.setString(8, order.getBuyerWords());
                    ps.setString(9, order.getSellerWords());
                    ps.setInt(10, 0);
                    ps.setString(11, order.getLogisticsCode());
                    ps.setLong(12, order.getShipTime());
                    ps.setLong(13, 0);
                    ps.setInt(14, order.getOrderStatus());
                    ps.setString(15, order.getCreateTime());
                    ps.setString(16, order.getExpShipTime());
                    ps.setLong(17, 0);
                    ps.setString(18, order.getCancelReason());
                    ps.setInt(19, order.getPayType());
                    ps.setString(20, order.getPayTime() == null ? "" : order.getPayTime());
                    ps.setDouble(21, order.getPostAmount() / 100);
                    ps.setDouble(22, 0);// order.getCouponAmount()
                    ps.setDouble(23, 0);// order.getShopCouponAmount()
                    ps.setString(24, order.getCouponInfo());
                    ps.setDouble(25, order.getOrderAmount() / 100);
                    ps.setInt(26, 0);
                    ps.setInt(27, order.getTradeType());
                    ps.setInt(28, 0);// order.getcType()
                    ps.setInt(29, order.getbType());
                    ps.setBigDecimal(30, new BigDecimal(0));
                    ps.setString(31, order.getProvince());
                    ps.setString(32, order.getCity());
                    ps.setString(33, order.getTown());
                    ps.setString(34, order.getStreet());
                    ps.setString(35, order.getEncryptDetail());
                    ps.setString(36, order.getEncrypt_post_tel());
                    ps.setString(37, order.getEncrypt_post_receiver());
                    ps.setString(38, order.getPhoneKey());
                    ps.setString(39, order.getAddressKey());
                    ps.setLong(40, System.currentTimeMillis() / 1000);
                    return ps;
                }
            }, keyHolder);

            Long orderId = keyHolder.getKey().longValue();

            /***** 1、添加dc_douyin_orders_items *****/
            String itemSQL = "INSERT INTO dc_douyin_orders_items (dc_douyin_orders_id,order_id,shop_id,product_id,product_name,product_pic,"
                    +
                    "combo_id,code,spec_desc,combo_num,post_amount,coupon_amount,coupon_meta_id,coupon_info," +
                    "campaign_info,total_amount,is_comment,goodsNumber,price,erpGoodsId,erpGoodsSpecId,isGift,author_id,author_name) VALUE (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

            String erpGoodSpecSQL = "select spec.*,eg.number as goodsNumber from erp_goods_spec spec LEFT JOIN erp_goods eg ON spec.goodsId=eg.id where spec.specNumber=? LIMIT 1";
            for (var item : order.getSkuOrderList()) {
                ErpGoodsSpecEntity spec = null;
                String goodsNumber = "";
                String specDesc = "";
                Integer goodsId = 0;
                Integer specId = 0;
                String couponInfo = "";
                if (item.getSpecCode().contains("GP00") || item.getSpecCode().contains("gp00")) {
                    goodsNumber = "GP";
                    goodsId = 925;
                    specId = 2457;
                    var specArray = JSONArray.parseArray(item.getSpec());
                    if (specArray.size() > 0) {
                        var obj = (JSONObject) specArray.get(0);
                        couponInfo = obj.getString("value");
                    }
                } else {
                    var specList = jdbcTemplate.query(erpGoodSpecSQL,
                            new BeanPropertyRowMapper<>(ErpGoodsSpecEntity.class), item.getSpecCode());
                    if (specList != null && specList.size() > 0) {
                        spec = specList.get(0);
                        goodsNumber = spec.getGoodsNumber();
                        goodsId = spec.getGoodsId();
                        specId = spec.getId();
                        specDesc = spec.getColorValue() + spec.getSizeValue();
                    }
                }
                double itemPrice = item.getOrderAmount() / 100;
                jdbcTemplate.update(itemSQL, orderId, item.getOrderId(), order.getShopId(), item.getProductId(),
                        item.getProductName(), item.getProductPic(),
                        0, item.getSpecCode(), specDesc, item.getItemNum(), 0, 0, 0, couponInfo,
                        0, itemPrice, 0, goodsNumber, 0, goodsId, specId, 0, item.getAuthorId(), item.getAuthorName());
            }
            return new ResultVo<>(EnumResultVo.SUCCESS, "成功");
        } catch (Exception e) {
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return new ResultVo<>(EnumResultVo.Fail, "异常：" + e.getMessage());
        }
    }

    /**
     * 查询订单信息
     *
     * @param orderId
     * @return
     */
    public DcDouyinOrdersListVo getOderDetailByOrderId(Long orderId) {
        List<DcDouyinOrdersListVo> lists = jdbcTemplate.query("select * from dc_douyin_orders where order_id=?",
                new BeanPropertyRowMapper<>(DcDouyinOrdersListVo.class), orderId);
                
        StringBuilder itemSql = new StringBuilder("select item.* ");
        // itemSql.append(", (SELECT l.number from erp_goods_stock_info s LEFT JOIN
        // erp_stock_location l on s.locationId=l.id WHERE s.isDelete=0 and
        // s.specNumber=item.code limit 1) as locationNumber ");
        itemSql.append(" from  dc_douyin_orders_items item where  order_id=?");
        lists.forEach(list -> list.setItems(jdbcTemplate.query(itemSql.toString(),
                new BeanPropertyRowMapper<>(DcDouyinOrdersItemsEntity.class), list.getOrderId())));
        if (lists != null && lists.size() > 0)
            return lists.get(0);
        return null;
    }

    public DcDouyinOrdersListVo getOderDetailByOrderIdForSend(Long orderId) {
        List<DcDouyinOrdersListVo> lists = jdbcTemplate.query("select * from dc_douyin_orders where order_id=?",
                new BeanPropertyRowMapper<>(DcDouyinOrdersListVo.class), orderId);
        StringBuilder itemSql = new StringBuilder("select item.* ");
        itemSql.append(
                ", (SELECT l.number from erp_goods_stock_info s LEFT JOIN erp_stock_location l on  s.locationId=l.id WHERE s.isDelete=0 and s.specNumber=item.code limit 1) as locationNumber ");
        itemSql.append(" from  dc_douyin_orders_items item where item_status <> 4 and  order_id=?");
        lists.forEach(list -> list.setItems(jdbcTemplate.query(itemSql.toString(),
                new BeanPropertyRowMapper<>(DcDouyinOrdersItemsEntity.class), list.getOrderId())));
        if (lists != null && lists.size() > 0)
            return lists.get(0);
        return null;
    }

    public List<DcDouyinOrdersItemsEntity> getOderDetailByPrint(String logisticsCode, Integer print) {

        // List<DcDouyinOrdersItemsEntity> lists=new ArrayList<>();
        StringBuilder SQL = new StringBuilder("select item.*, ");
        SQL.append(
                " (SELECT l.number from erp_goods_stock_info s LEFT JOIN erp_stock_location l on  s.locationId=l.id WHERE s.isDelete=0 and s.specNumber=item.code limit 1) as locationNumber, ");
        SQL.append(
                " o.order_id,o.logistics_code,o.logistics_company,o.result,o.logistics_id,o.buyer_words,o.seller_words ");
        SQL.append(
                " from dc_douyin_orders_items item left join dc_douyin_orders o on item.dc_douyin_orders_id=o.id where item.item_status <> 4 and o.printStatus=? and o.logistics_code=? ");

        return jdbcTemplate.query(SQL.toString(), new BeanPropertyRowMapper<>(DcDouyinOrdersItemsEntity.class), print,
                logisticsCode);
    }

    public void updOrderPrintByCode(String logisticsCode, Integer orderStatus, String result, Integer print) {
        jdbcTemplate.update("update dc_douyin_orders set order_status=?,printStatus=?,result=? where logistics_code=? ",
                orderStatus, print, result, logisticsCode);
        if (print == 2) {
            jdbcTemplate.update("update dc_douyin_orders set printDate=? where logistics_code=? ",
                    DateUtil.getCurrentDate(), logisticsCode);
        }
    }

    /**
     * 分页查询订单列表
     *
     * @param pageIndex
     * @param pageSize
     * @param orderNum
     * @param state
     * @return
     */
    @Transactional
    public PagingResponse<DcDouyinOrdersListVo> getDouyinOrders(Long shopId, Integer pageIndex,
            Integer pageSize, String orderNum, Integer startTime, Integer endTime, Integer state,Integer auditStatus, String logisticsCode) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT SQL_CALC_FOUND_ROWS  A.*,sh.`name` as shopName ");
        sb.append(" FROM dc_douyin_orders A ");
        sb.append(" LEFT JOIN dc_shop sh on sh.id=A.shop_id ");

        sb.append(" WHERE 1=1 ");
        List<Object> params = new ArrayList<>();
       
            
            
        if(shopId!=null && shopId > 0){
            sb.append(" AND A.shop_id=? ");
            params.add(shopId);
        }
        if (!StringUtils.isEmpty(orderNum)) {
            sb.append("AND (A.order_id = ? OR A.order_id = ? ) ");
            params.add(orderNum);
            params.add(orderNum + "A");
        }

        if (!StringUtils.isEmpty(logisticsCode)) {
            sb.append("AND A.logistics_code=? ");
            params.add(logisticsCode.trim());
        }

        if (startTime!=null && startTime >0) {
            sb.append("AND A.create_time > ? ");
            params.add(startTime);
        }
        if (endTime!=null && endTime >0) {
            sb.append("AND A.create_time <= ? ");
            params.add(endTime);
        }

        if (state != null) {
            sb.append("AND  A.order_status = ?  ");
            params.add(state);
        }
        if (auditStatus != null) {
            if(auditStatus == 0){
                sb.append("AND  A.auditStatus = 0 AND A.order_status <> 9  ");
               
            }else{
                sb.append("AND  A.auditStatus = ?  ");
                params.add(auditStatus);
            }
            
        }
        
        sb.append("ORDER BY A.create_time DESC LIMIT ?,?");
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);

        List<DcDouyinOrdersListVo> lists = jdbcTemplate.query(sb.toString(),
                new BeanPropertyRowMapper<>(DcDouyinOrdersListVo.class), params.toArray(new Object[params.size()]));
        int totalSize = getTotalSize();
        StringBuilder itemSql = new StringBuilder();
        itemSql.append(
                "(SELECT IFNULL(SUM(currentQty),0)  FROM erp_goods_stock_info WHERE specNumber=poi.goodsSpecNum and isDelete=0) as currentQty, ");
        itemSql.append(
                "(SELECT IFNULL(SUM(it1.quantity),0) FROM erp_order_item AS it1 WHERE it1.skuNumber=poi.goodsSpecNum AND  it1.`status` <= 1) pickingQty ");
        itemSql.append("from dc_douyin_orders_items item where dc_douyin_orders_id= ");

        String sql = "select item.*,egs.color_image as erpGoodsSpecImg from dc_douyin_orders_items item LEFT JOIN erp_goods_spec egs on egs.id = item.erpGoodsSpecId where item.dc_douyin_orders_id=?";
        lists.forEach(list -> list.setItems(
                jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DcDouyinOrdersItemsEntity.class), list.getId())));
        return new PagingResponse<>(pageIndex, pageSize, totalSize, lists);
    }

    public List<DcDouyinOrdersListVo> getDouyinOrders(Integer shopId, Integer startTime, Integer endTime,
            Integer status) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT  A.* ");
        sb.append(" FROM dc_douyin_orders A ");
        sb.append(" WHERE shop_id=? ");
        List<Object> params = new ArrayList<>();
        params.add(shopId);
        if (status != null) {
            sb.append("AND  A.order_status = ?  ");
            params.add(status);
        }
        if (!StringUtils.isEmpty(startTime)) {
            sb.append("AND A.create_time > ? ");
            params.add(startTime);
        }
        if (!StringUtils.isEmpty(endTime)) {
            sb.append("AND A.create_time <= ? ");
            params.add(endTime);
        }
        sb.append("ORDER BY A.create_time DESC ");
        List<DcDouyinOrdersListVo> lists = jdbcTemplate.query(sb.toString(),
                new BeanPropertyRowMapper<>(DcDouyinOrdersListVo.class), params.toArray(new Object[params.size()]));
        String sql = "select item.*,eg.`name` as erpGoodsName from dc_douyin_orders_items item left join erp_goods eg on eg.id = item.erpGoodsId where item.dc_douyin_orders_id=? ";
        lists.forEach(list -> list.setItems(
                jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DcDouyinOrdersItemsEntity.class), list.getId())));
        return lists;
    }

    public PagingResponse<DcDouyinOrdersItemsEntity> getDouyinPrintOrders(Integer dyShopId, Integer pageIndex,
            Integer pageSize, String orderNum, Integer startTime, Integer endTime, String goodsNum, String skuNum,
            Integer print, String code, String startDate, String endDate, Integer isBz) {

        StringBuilder itemSql = new StringBuilder(
                "select item.id,item.combo_num,item.goodsNumber,item.spec_desc as spec,item.code,item.product_name,item.parent_order_id");
        itemSql.append(",item.order_id,item.shop_id");
        itemSql.append(",o.province,o.city,o.town,o.exp_ship_time,o.order_status,o.logistics_code,");
        itemSql.append(
                "o.logistics_company,o.result,o.logistics_id,o.printDate,o.seller_words,o.buyer_words,o.create_time,  ");
        itemSql.append("egs.color_image as product_pic,");
        itemSql.append(
                "(SELECT IFNULL(SUM(currentQty),0)  FROM erp_goods_stock_info WHERE specId=item.erpGoodsSpecId and isDelete=0) as currentQty, ");
        itemSql.append(
                "(SELECT IFNULL(SUM(it1.quantity),0) FROM erp_order_item AS it1 WHERE it1.skuNumber=item.code AND  it1.`status` <= 1) pickingQty ");
        itemSql.append(
                " from dc_douyin_orders_items item left join dc_douyin_orders o on item.dc_douyin_orders_id=o.id ");
        itemSql.append(" left join erp_goods_spec egs on egs.id = item.erpGoodsSpecId ");

        itemSql.append("where item.item_status <> 4 and o.shop_id=? ");
        List<Object> params = new ArrayList<>();
        params.add(dyShopId);

        if (!StringUtils.isEmpty(orderNum)) {
            itemSql.append("AND (o.order_id = ? OR o.order_id = ? ) ");
            params.add(orderNum);
            params.add(orderNum + "A");
        }
        if (isBz >= 0) {
            itemSql.append("AND (LENGTH(o.buyer_words)>0 OR LENGTH(o.seller_words)>0 ) ");
        }

        if (!StringUtils.isEmpty(goodsNum)) {
            itemSql.append(" AND item.goodsNumber=? ");
            params.add(goodsNum);
        }
        if (!StringUtils.isEmpty(skuNum)) {
            itemSql.append(" AND item.code=? ");
            params.add(skuNum);
        }

        if (!StringUtils.isEmpty(print)) {
            if (print == 0 || print == 4) {
                // itemSql.append(" AND (o.printStatus=0 or o.printStatus=4 ) and
                // (o.order_status=105 or o.order_status=2) ");
                itemSql.append(" AND o.printStatus=?  and (o.order_status=105 or o.order_status=2)  ");
                params.add(print);
            } else if (print == 1) {
                itemSql.append(" AND o.printStatus=1 ");
            } else {
                if (StringUtils.isEmpty(code) && StringUtils.isEmpty(startDate)) {
                    itemSql.append(" AND o.printStatus=? and  (o.order_status=105 or o.order_status=2) ");
                    params.add(print);
                } else {
                    if (!StringUtils.isEmpty(code)) {
                        itemSql.append(" AND o.logistics_code=? ");
                        params.add(code);
                    }
                    if (!StringUtils.isEmpty(startDate)) {
                        itemSql.append(" AND  o.printDate >= ? ");
                        params.add(startDate);
                    }
                    if (!StringUtils.isEmpty(endDate)) {
                        itemSql.append(" AND o.printDate <= ? ");
                        params.add(endDate);
                    }
                }
            }
        }

        if (!StringUtils.isEmpty(startTime)) {
            itemSql.append("AND o.create_time > ? ");
            params.add(startTime);
        }
        if (!StringUtils.isEmpty(endTime)) {
            itemSql.append("AND o.create_time <= ? ");
            params.add(endTime);
        }
        itemSql.append("ORDER BY o.exp_ship_time  LIMIT ?,?");
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);

        List<DcDouyinOrdersItemsEntity> lists = jdbcTemplate.query(itemSql.toString(),
                new BeanPropertyRowMapper<>(DcDouyinOrdersItemsEntity.class),
                params.toArray(new Object[params.size()]));
        int totalSize = getTotalSize();
        return new PagingResponse<>(pageIndex, pageSize, totalSize, lists);
    }

    public List<DcDouyinOrdersListVo> getDouyinOrderHebing(Integer dyShopId) {
        List<Object> params = new ArrayList<>();
        List<DcDouyinOrdersListVo> list = new ArrayList<>();
        StringBuilder sb = new StringBuilder(
                "select order_id,phoneKey,addressKey from dc_douyin_orders WHERE shop_id=? and order_status=2 AND printStatus=0 AND LENGTH(phoneKey)>0 ");

        sb.append(" group by phoneKey,addressKey having count(0) >1 LIMIT 30");

        params.add(dyShopId);

        var hebingList = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(DcDouyinOrdersListVo.class),
                params.toArray(new Object[params.size()]));

        for (var order : hebingList) {
            var hebing = jdbcTemplate.query("select * from dc_douyin_orders WHERE order_id=?",
                    new BeanPropertyRowMapper<>(DcDouyinOrdersListVo.class), order.getOrderId());
            List<Object> paramsItem = new ArrayList<>();
            StringBuilder itemSql = new StringBuilder(
                    "select item.*,o.province,o.city,o.town,o.exp_ship_time,o.order_status,o.logistics_code,o.logistics_company,o.result,o.logistics_id,o.printDate,o.seller_words,o.buyer_words,  ");
            itemSql.append(
                    "(SELECT IFNULL(SUM(currentQty),0)  FROM erp_goods_stock_info WHERE specNumber=item.code and isDelete=0) as currentQty, ");
            itemSql.append(
                    "(SELECT IFNULL(SUM(it1.quantity),0) FROM erp_order_item AS it1 WHERE it1.skuNumber=item.code AND  it1.`status` <= 1) pickingQty ");
            itemSql.append(
                    " from dc_douyin_orders_items item left join dc_douyin_orders o on item.dc_douyin_orders_id=o.id  ");
            itemSql.append(
                    " where o.shop_id=?  and  order_status=2 AND printStatus=0 AND LENGTH(phoneKey)>0 and o.phoneKey=? and o.addressKey=? ");
            paramsItem.add(dyShopId);
            paramsItem.add(order.getPhoneKey());
            paramsItem.add(order.getAddressKey());

            hebing.get(0)
                    .setItems(jdbcTemplate.query(itemSql.toString(),
                            new BeanPropertyRowMapper<>(DcDouyinOrdersItemsEntity.class),
                            paramsItem.toArray(new Object[paramsItem.size()])));
            list.add(hebing.get(0));
        }
        return list;
    }

    /**
     * 订单导出
     *
     * @param startTime
     * @param endTime
     * @return
     */
    public List<DcDouyinOrdersItemsEntity> getDouyinOrdersExport(Integer startTime, Integer endTime, Integer status) {

        StringBuilder itemSql = new StringBuilder(
                "SELECT o.order_id,i.goodsNumber,i.spec_code,i.spec_desc,i.combo_num,i.price,it.purPrice couponAmount,i.spec_desc,i.total_amount, i.author_id,i.author_name,");
        itemSql.append(
                "FROM_UNIXTIME(o.create_time,'%Y-%m-%d %H:%i:%s') printDate from dc_douyin_orders_items i LEFT JOIN dc_douyin_orders o  on o.id=i.dc_douyin_orders_id ");
        itemSql.append(" LEFT JOIN erp_order_item it ON it.aliSubItemID=i.id  ");
        itemSql.append("where 1=1 ");
        List<Object> params = new ArrayList<>();
        if (status != null && status.intValue() > 0) {
            // (o.order_status=1 OR o.order_status=2 OR o.order_status=3 OR
            // o.order_status=5)
            itemSql.append(" AND o.order_status=? ");
            params.add(status);
        }

        if (!StringUtils.isEmpty(startTime)) {
            itemSql.append("AND o.create_time >= ? ");
            params.add(startTime);
        }
        if (!StringUtils.isEmpty(endTime)) {
            itemSql.append("AND o.create_time <= ? ");
            params.add(endTime);
        }
        itemSql.append("ORDER BY o.create_time DESC ");

        List<DcDouyinOrdersItemsEntity> lists = jdbcTemplate.query(itemSql.toString(),
                new BeanPropertyRowMapper<>(DcDouyinOrdersItemsEntity.class),
                params.toArray(new Object[params.size()]));
        return lists;
    }

    /**
     * 修改订单商品
     *
     * @param orderItemId
     * @param erpGoodSpecId
     * @param quantity
     * @return
     */
    public ResultVo<Long> updOrderSpec(Long orderItemId, Integer erpGoodSpecId, Integer quantity) {
        if (orderItemId == null || orderItemId.longValue() <= 0) {
            return new ResultVo<>(EnumResultVo.ParamsError, "参数错误，缺少orderItemId");
        }

        if (erpGoodSpecId == null || erpGoodSpecId.intValue() <= 0) {
            return new ResultVo<>(EnumResultVo.ParamsError, "参数错误，缺少erpGoodSpecId");
        }
        if (quantity == null || quantity.intValue() <= 0) {
            return new ResultVo<>(EnumResultVo.ParamsError, "参数错误，缺少quantity");
        }

        String sql = "SELECT * FROM dc_douyin_orders WHERE id = (SELECT dc_douyin_orders_id FROM dc_douyin_orders_items WHERE id=? )";
        try {
            var order = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(DcDouyinOrdersEntity.class),
                    orderItemId);
            if (order.getAuditStatus().intValue() != 0)
                return new ResultVo<>(EnumResultVo.DataError, "订单已经确认，不允许修改");

            if (order.getSendStatus().intValue() != 0)
                return new ResultVo<>(EnumResultVo.DataError, "订单不是待发货状态，不允许修改");

            var orderItem = jdbcTemplate.queryForObject("SELECT * FROM dc_douyin_orders_items WHERE id=?",
                    new BeanPropertyRowMapper<>(DcDouyinOrdersItemsEntity.class), orderItemId);

            /**************************/
            String erpGoodSpecSQL = "SELECT s.*,g.name as goodTitle,g.number as goodsNumber from " + Tables.ErpGoodsSpec
                    + " s LEFT JOIN " + Tables.ErpGoods + " g ON g.id=s.goodsId WHERE s.id=?";
            var erpOrderSpec = jdbcTemplate.queryForObject(erpGoodSpecSQL,
                    new BeanPropertyRowMapper<>(ErpGoodsSpecEntity.class), erpGoodSpecId);

            String remark = "修改商品，原sku：" + orderItem.getErpGoodsSpecId() + "(" + orderItem.getSpecCode() + ")数量:"
                    + orderItem.getComboNum() + "，新sku:" + erpOrderSpec.getId() + "(" + erpOrderSpec.getSpecNumber()
                    + ")数量：" + quantity;

            jdbcTemplate.update(
                    "UPDATE dc_douyin_orders_items SET erpGoodsId=?,erpGoodsSpecId=?,code=?,combo_num=?,goodsNumber=?,remark=? WHERE id=?",
                    erpOrderSpec.getGoodsId(), erpOrderSpec.getId(), erpOrderSpec.getSpecNumber(), quantity,
                    erpOrderSpec.getGoodsNumber(), remark, orderItemId);

            return new ResultVo<>(EnumResultVo.SUCCESS, "成功");
        } catch (Exception e) {
            return new ResultVo<>(EnumResultVo.ParamsError, "参数错误，不存在orderItem");
        }
    }

    /**
     * 订单发货
     *
     * @param orderId
     */
    public void updDouyinOrderStatus(String orderId, Integer orderStatus) {
        jdbcTemplate.update("update dc_douyin_orders set order_status=? where order_id=?", orderStatus, orderId);
    }

    /**
     * @param orderId
     * @param erpGoodsId
     * @param erpGoodSpecId
     * @param quantity
     * @return
     */
    public ResultVo<Long> addOrderGift(Long orderId, Integer erpGoodsId, Integer erpGoodSpecId, Integer quantity) {
        if (orderId == null || orderId.longValue() <= 0) {
            return new ResultVo<>(EnumResultVo.ParamsError, "参数错误，缺少orderId");
        }
        if (erpGoodsId == null || erpGoodsId.intValue() <= 0) {
            return new ResultVo<>(EnumResultVo.ParamsError, "参数错误，缺少erpGoodsId");
        }
        if (erpGoodSpecId == null || erpGoodSpecId.intValue() <= 0) {
            return new ResultVo<>(EnumResultVo.ParamsError, "参数错误，缺少erpGoodSpecId");
        }
        if (quantity == null || quantity.intValue() <= 0) {
            return new ResultVo<>(EnumResultVo.ParamsError, "参数错误，缺少quantity");
        }

        String sql = "SELECT * FROM " + Tables.DcDouyinOrder + " WHERE id =? ";
        try {
            var order = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(DcDouyinOrdersEntity.class),
                    orderId);

            if (order.getAuditStatus().intValue() != 0)
                return new ResultVo<>(EnumResultVo.DataError, "订单已经确认，不允许添加赠品");
            if (order.getSendStatus().intValue() != EnumErpOrderSendStatus.WaitOut.getIndex())
                return new ResultVo<>(EnumResultVo.DataError, "订单不是待发货状态，不允许添加赠品");

            /**************************/
            String erpGoodSpecSQL = "SELECT s.*,g.name as goodTitle,g.number as goodsNumber from " + Tables.ErpGoodsSpec
                    + " s LEFT JOIN " + Tables.ErpGoods + " g ON g.id=s.goodsId WHERE s.id=?";
            var erpGoodsSpec = jdbcTemplate.queryForObject(erpGoodSpecSQL,
                    new BeanPropertyRowMapper<>(ErpGoodsSpecEntity.class), erpGoodSpecId);

            String remark = "添加赠品" + erpGoodsSpec.getId() + "(" + erpGoodsSpec.getSpecNumber() + ")数量：" + quantity;
            String specName = "";
            if (StringUtils.isEmpty(erpGoodsSpec.getColorValue()) == false) {
                specName += "颜色：" + erpGoodsSpec.getColorValue();
            }
            if (StringUtils.isEmpty(erpGoodsSpec.getSizeValue()) == false) {
                specName += "尺码：" + erpGoodsSpec.getSizeValue();
            }

            jdbcTemplate.update("INSERT INTO  " + Tables.DcDouyinOrderItem
                    + " SET dc_douyin_orders_id=?,order_id=?,shop_id=?,parent_order_id=?," +
                    "erpGoodsId=?,erpGoodsSpecId=?,product_name=?,product_pic=?,goodsNumber=?,spec_desc=?,code=?,total_amount=0,price=0,combo_num=?,remark=?,isGift=1",
                    orderId, order.getOrderId(), order.getShopId(), order.getOrderId(), erpGoodsSpec.getGoodsId(),
                    erpGoodsSpec.getId(), erpGoodsSpec.getGoodTitle(), erpGoodsSpec.getColorImage(),
                    erpGoodsSpec.getGoodsNumber(), specName, erpGoodsSpec.getSpecNumber(), quantity, remark);

            return new ResultVo<>(EnumResultVo.SUCCESS, "成功");
        } catch (Exception e) {
            return new ResultVo<>(EnumResultVo.ParamsError, "参数错误，不存在order");
        }
    }

    /**
     * 添加退货订单
     *
     * @param order
     * @return
     */
    @Transactional(rollbackFor = Exception.class)
    public ResultVo<Integer> editDouYinRefundOrder(DcDouyinOrdersRefundEntity order) {

        // 拦截 || 退货
        addErpReturnOrder(order);

        Integer count = jdbcTemplate.queryForObject("select count(0) from dc_douyin_orders_refund where aftersale_id=?",
                Integer.class, order.getAftersaleId());
        if (count > 0) {
            jdbcTemplate.update(
                    "update dc_douyin_orders_refund set total_amount=?,question_desc=?,logistics_company=?,logistics_code=?,refund_status=? where aftersale_id=?",
                    order.getTotalAmount(), order.getQuestionDesc(), order.getLogisticsCompany(),
                    order.getLogisticsCode(), order.getRefundStatus(), order.getAftersaleId());
            return new ResultVo<>(EnumResultVo.SUCCESS, "已存在");
        }
        /***** 1、添加order *****/
        StringBuilder orderInsertSQL = new StringBuilder();
        orderInsertSQL.append("INSERT INTO dc_douyin_orders_refund");
        orderInsertSQL.append(" SET ");
        orderInsertSQL.append(" order_id=?,");
        orderInsertSQL.append(" shop_id=?,");
        orderInsertSQL.append(" user_name=?,");
        orderInsertSQL.append(" post_addr=?,");
        orderInsertSQL.append(" post_code=?,");
        orderInsertSQL.append(" post_receiver=?,");
        orderInsertSQL.append(" post_tel=?,");
        orderInsertSQL.append(" buyer_words=?,");
        orderInsertSQL.append(" seller_words=?,");
        orderInsertSQL.append(" logistics_id=?,");
        orderInsertSQL.append(" logistics_code=?,");
        orderInsertSQL.append(" logistics_time=?,");
        orderInsertSQL.append(" receipt_time=?,");
        orderInsertSQL.append(" final_status=?,");
        orderInsertSQL.append(" order_status=?,");
        orderInsertSQL.append(" create_time=?,");
        orderInsertSQL.append(" update_time=?,");
        orderInsertSQL.append(" cancel_reason=?,");
        orderInsertSQL.append(" pay_type=?,");
        orderInsertSQL.append(" pay_time=?,");
        orderInsertSQL.append(" post_amount=?,");
        orderInsertSQL.append(" total_amount=?,");
        orderInsertSQL.append(" spec_desc=?,");
        orderInsertSQL.append(" product_name=?,");
        orderInsertSQL.append(" product_pic=?,");
        orderInsertSQL.append(" code=?,");
        orderInsertSQL.append(" combo_num=?,");
        orderInsertSQL.append(" pid=?, ");
        orderInsertSQL.append(" createOn=?, ");
        orderInsertSQL.append(" question_desc=?, ");
        orderInsertSQL.append(" apply_time=?, ");
        orderInsertSQL.append(" logistics_company=?, ");
        orderInsertSQL.append(" aftersale_type=?, ");
        orderInsertSQL.append(" aftersale_id=?, ");
        orderInsertSQL.append(" refund_status=? ");
        try {
            KeyHolder keyHolder = new GeneratedKeyHolder();
            jdbcTemplate.update(new PreparedStatementCreator() {
                @Override
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    PreparedStatement ps = connection.prepareStatement(orderInsertSQL.toString(),
                            Statement.RETURN_GENERATED_KEYS);
                    ps.setString(1, order.getOrderId());
                    ps.setLong(2, order.getShopId());
                    ps.setString(3, order.getUserName());
                    ps.setString(4, order.getPostAddr());
                    ps.setString(5, order.getPostCode());
                    ps.setString(6, order.getPostReceiver());
                    ps.setString(7, order.getPostTel());
                    ps.setString(8, order.getBuyerWords());
                    ps.setString(9, order.getSellerWords());
                    ps.setInt(10, order.getLogisticsId());
                    ps.setString(11, order.getLogisticsCode());
                    ps.setLong(12, order.getLogisticsTime());
                    ps.setLong(13, order.getReceiptTime());
                    ps.setInt(14, order.getFinalStatus());
                    ps.setInt(15, order.getOrderStatus());
                    ps.setLong(16, order.getCreateTime());
                    ps.setLong(17, order.getUpdateTime());
                    ps.setString(18, order.getCancelReason());
                    ps.setInt(19, order.getPayType());
                    ps.setLong(20, StringUtils.isEmpty(order.getPayTime()) ? 0l : order.getPayTime());
                    ps.setDouble(21, order.getPostAmount() > 0 ? order.getPostAmount() / 100 : 0);
                    ps.setDouble(22, order.getTotalAmount() / 100);
                    ps.setString(23, order.getSpecDesc());
                    ps.setString(24, order.getProductName());
                    ps.setString(25, order.getProductPic());
                    ps.setString(26, order.getCode());
                    ps.setInt(27, order.getComboNum());
                    ps.setString(28, StringUtils.isEmpty(order.getPid()) ? order.getOrderId() : order.getPid());
                    ps.setLong(29, System.currentTimeMillis() / 1000);
                    ps.setString(30, order.getQuestionDesc());
                    ps.setLong(31, order.getApplyTime());
                    ps.setString(32, order.getLogisticsCompany());
                    ps.setInt(33, order.getAftersaleType());
                    ps.setLong(34, order.getAftersaleId());
                    ps.setInt(35, order.getRefundStatus());
                    return ps;
                }
            }, keyHolder);

            Long orderId = keyHolder.getKey().longValue();

            /***** 1、添加dc_douyin_orders_items *****/
            String itemSQL = "INSERT INTO dc_douyin_orders_refund_items set refund_id=?,order_id=?,logistics_code=?,product_name=?,product_image=?,shop_sku_code=?,item_num=?,price=?";

            for (var item : order.getItems()) {
                jdbcTemplate.update(itemSQL, orderId, item.getSku_order_id(), item.getLogistics_code(),
                        item.getProduct_name(), item.getProduct_image(), item.getShop_sku_code(), item.getItem_num(),
                        item.getPrice() / 100);
            }
            return new ResultVo<>(EnumResultVo.SUCCESS, "成功");
        } catch (Exception e) {
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return new ResultVo<>(EnumResultVo.Fail, "异常：" + e.getMessage());
        }
        // return new ResultVo<>(EnumResultVo.SUCCESS,"成功");
    }

    @Transactional(rollbackFor = Exception.class)
    public ResultVo<Integer> addErpReturnOrder(DcDouyinOrdersRefundEntity refundOrder) {
        String code = refundOrder.getItems().get(0).getShop_sku_code();

        /*
         * if(refundOrder.getAftersaleType()==1 && code.trim().length()>5){
         * try {
         * String logisticsCode = jdbcTemplate.
         * queryForObject("SELECT logistics_code FROM dc_douyin_orders WHERE order_id=? "
         * ,String.class, refundOrder.getOrderId());
         * refundOrder.setLogisticsCode(logisticsCode);
         * }catch (Exception e){
         * 
         * }
         * }
         */
        if (!StringUtils.isEmpty(refundOrder.getLogisticsCode())) {// && code.trim().length()>5
            Integer erpOrderReturnCount = jdbcTemplate.queryForObject(
                    "SELECT COUNT(0) FROM " + Tables.ErpOrderReturn + " WHERE order_num=? AND shopId=?", Integer.class,
                    refundOrder.getAftersaleId(), 8);
            if (erpOrderReturnCount > 0)
                return new ResultVo<>(EnumResultVo.Fail, "仓库已经存在该退货单");
            /*********** 插入仓库系统退款订单 erp_order_return erp_order_return_item *************/
            try {
                // 插入仓库退货订单 erp_order_return
                String returnOrder = "INSERT INTO " + Tables.ErpOrderReturn +
                        " (order_num,orderTime,createTime,contactPerson,mobile,address,source,status,logisticsCompany,logisticsCompanyCode,logisticsCode,source_order_num,shopId,remark)"
                        +
                        " VALUE (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

                KeyHolder keyHolder = new GeneratedKeyHolder();

                jdbcTemplate.update(new PreparedStatementCreator() {
                    @Override
                    public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                        PreparedStatement ps = connection.prepareStatement(returnOrder,
                                Statement.RETURN_GENERATED_KEYS);
                        ps.setString(1, String.valueOf(refundOrder.getAftersaleId()));
                        ps.setLong(2, refundOrder.getApplyTime());
                        ps.setLong(3, System.currentTimeMillis() / 1000);
                        ps.setString(4, "");
                        ps.setString(5, "");
                        ps.setString(6, "");
                        ps.setString(7, ErpOrderSourceEnum.DOUYIN.getIndex());
                        ps.setInt(8, EnumOrderReturnStatus.WaitReceive.getIndex());
                        ps.setString(9, "");
                        ps.setString(10, "");
                        ps.setString(11, refundOrder.getLogisticsCode());
                        ps.setString(12, refundOrder.getOrderId());
                        ps.setLong(13, 8);
                        ps.setString(14, refundOrder.getBuyerWords());
                        return ps;
                    }
                }, keyHolder);
                Long returnOrderId = keyHolder.getKey().longValue();

                // 插入仓库退货订单明细 erp_order_return_item
                String returnOrderItemSQL = "INSERT INTO " + Tables.ErpOrderReturnItem
                        + " (orderId,goodsId,skuId,skuNumber,quantity,receiveType,createTime,status,inQuantity,badQuantity,refundAmount,stockInfoItemId) VALUE (?,?,?,?,?,?,?,?,?,?,?,?)";

                for (var ritem : refundOrder.getItems()) {
                    Integer goodsId = 0;
                    Integer specId = 0;
                    Long stockInfoItemId = 0L;
                    try {
                        // 查询仓库系统sku
                        var sku = jdbcTemplate.queryForObject(
                                "select * from " + Tables.ErpGoodsSpec + " WHERE specNumber=? limit 1",
                                new BeanPropertyRowMapper<>(ErpGoodsSpecEntity.class), ritem.getShop_sku_code());
                        stockInfoItemId = jdbcTemplate.queryForObject(
                                "SELECT IFNULL((SELECT stockInfoItemId  FROM erp_order_item i LEFT JOIN erp_order e ON i.orderId=e.id WHERE e.order_num=? AND  i.skuId=? LIMIT 1),0) id",
                                Long.class, refundOrder.getOrderId(), sku.getId());
                    } catch (Exception e) {
                        goodsId = 0;
                        specId = 0;
                        stockInfoItemId = 0L;
                    }
                    /****** 插入仓库系统退货商品信息 *******/
                    jdbcTemplate.update(returnOrderItemSQL, returnOrderId, goodsId, specId, ritem.getShop_sku_code(),
                            refundOrder.getComboNum(), 0, System.currentTimeMillis() / 1000, 0, 0, 0,
                            ritem.getPrice() * refundOrder.getComboNum(), stockInfoItemId);
                }
                return new ResultVo<>(EnumResultVo.SUCCESS, "成功");
            } catch (Exception e) {
                TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                System.out.println(refundOrder.getOrderId() + "退货失败");
                return new ResultVo<>(EnumResultVo.Fail, "抖音退货确认到仓库失败");
            }
        }
        return new ResultVo<>(EnumResultVo.SUCCESS, "成功");
    }

    /**
     * 分页查退货订单列表
     *
     * @param pageIndex
     * @param pageSize
     * @param state
     * @return
     */
    public PagingResponse<DouyinOrdersRefundEntity> getDouyinRefundOrders(Integer shopId, Integer pageIndex,
            Integer pageSize, String orderNum, String logisticsCode, Integer startTime, Integer endTime, Integer state,
            Integer aftersaleType) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT SQL_CALC_FOUND_ROWS  R.* ");
        sb.append(" FROM dc_douyin_orders_refund R ");
        // sb.append(" left join dc_douyin_orders_items d on R.order_id=d.order_id");
        sb.append(" WHERE shop_id=? ");
        List<Object> params = new ArrayList<>();
        params.add(shopId);

        if (!StringUtils.isEmpty(orderNum)) {
            sb.append("AND R.order_id = ? ");
            params.add(orderNum);
        }

        if (!StringUtils.isEmpty(startTime)) {
            sb.append("AND R.apply_time > ? ");
            params.add(startTime);
        }
        if (!StringUtils.isEmpty(endTime)) {
            sb.append("AND R.apply_time <= ? ");
            params.add(endTime);
        }

        if (!StringUtils.isEmpty(state)) {
            sb.append("AND  R.refund_status = ?  ");
            params.add(state);
        }
        if (!StringUtils.isEmpty(aftersaleType)) {
            sb.append("AND  R.aftersale_type = ?  ");
            params.add(aftersaleType);
        }
        if (!StringUtils.isEmpty(logisticsCode)) {
            sb.append(" AND R.logistics_code = ?  ");
            params.add(logisticsCode);
        }
        sb.append("ORDER BY R.createOn DESC LIMIT ?,?");
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);

        List<DouyinOrdersRefundEntity> lists = jdbcTemplate.query(sb.toString(),
                new BeanPropertyRowMapper<>(DouyinOrdersRefundEntity.class),
                params.toArray(new Object[params.size()]));
        Integer total = getTotalSize();
        // lists.forEach(s->s.setItems(jdbcTemplate.query("select * from
        // dc_douyin_orders_refund_items where refund_id=?", new
        // BeanPropertyRowMapper<>(DcDouyinOrdersRefundItemEntity.class),s.getId())));
        return new PagingResponse<>(pageIndex, pageSize, total, lists);
    }

    /**
     * 获取售后订单详情
     *
     * @param refundId
     * @return
     */
    public DouyinOrdersRefundEntity getDouYinRefundOrderDetail(Long refundId) {
        // String sql = "SELECT r.*,d.code,d.goodsNumber,d.combo_num as buyQuantity from
        // dc_douyin_orders_refund r left join dc_douyin_orders_items d on
        // r.order_id=d.order_id where r.aftersale_id=?";
        // StringBuilder sb = new StringBuilder("SELECT ");
        // sb.append(
        //         "r.id,r.order_id,r.aftersale_id,r.logistics_code,r.logistics_company,r.logistics_time,r.combo_num,r.total_amount,r.createOn,");
        // sb.append("r.question_desc,r.apply_time,r.aftersale_type,r.stockStatus,r.refund_status,");
        // sb.append("ri.product_name,ri.product_image,ri.shop_sku_code,ri.item_num,ri.price");

        // sb.append(" from dc_douyin_orders_refund  r ");

        // sb.append(" LEFT JOIN dc_douyin_orders_refund_items ri ON ri.refund_id = r.id");

        // sb.append(" where r.id=?");
        // List<DcDouyinOrdersRefundEntity> lists = jdbcTemplate.query(sb.toString(),
        //         new BeanPropertyRowMapper<>(DcDouyinOrdersRefundEntity.class), refundId);
        // if (lists != null && lists.size() > 0)
        //     return lists.get(0);
        // return null;
        try {
            return jdbcTemplate.queryForObject("SELECT * FROM dc_douyin_orders_refund WHERE id=?", new BeanPropertyRowMapper<>(DouyinOrdersRefundEntity.class),refundId);
        } catch (Exception e) {
            return null;
        }
    }

    /**
     * 确认退货并到仓库
     *
     * @param id
     * @param
     */
    @Transactional(rollbackFor = Exception.class)
    public ResultVo<Integer> confirmRefund(Long id, String logisticsCompany, String logisticsCode) {
        try {
            // 查询退款单
            String sql = "SELECT * FROM dc_douyin_orders_refund WHERE id=?";
            DcDouyinOrdersRefundEntity refundOrder = jdbcTemplate.queryForObject(sql,
                    new BeanPropertyRowMapper<>(DcDouyinOrdersRefundEntity.class), id);

            if (refundOrder == null)
                return new ResultVo<>(EnumResultVo.NotFound, "退货订单不存在");// 订单不存在return 404;
            if (refundOrder.getAuditStatus().intValue() == 1)
                return new ResultVo<>(EnumResultVo.HasAssociatedData, "已经处理过了，不能重复处理");
            // if(refundOrder.getAfterType().intValue() !=0) return new
            // ResultVo<>(EnumResultVo.HasAssociatedData, "不是退货退款订单，不能处理");

            // 没有找到买家发货物流
            if (StringUtils.isEmpty(logisticsCompany))
                return new ResultVo<>(EnumResultVo.DataError, "没有找到买家发货物流公司");
            if (StringUtils.isEmpty(logisticsCode))
                return new ResultVo<>(EnumResultVo.DataError, "没有找到买家发货物流");

            /********* 查询对应的订单 *********/
            DcDouyinOrdersEntity order = null;
            DcDouyinOrdersItemsEntity orderItem = null;
            try {
                // 查询关联的订单
                order = jdbcTemplate.queryForObject("SELECT * FROM dc_douyin_orders WHERE order_id=?",
                        new BeanPropertyRowMapper<>(DcDouyinOrdersEntity.class), refundOrder.getPid());
                orderItem = jdbcTemplate.queryForObject(
                        "select * from dc_douyin_orders_items WHERE dc_douyin_orders_id=? and order_id=? ",
                        new BeanPropertyRowMapper<>(DcDouyinOrdersItemsEntity.class), order.getId(),
                        refundOrder.getOrderId());

                if (orderItem.getErpGoodsId().intValue() == 0 || orderItem.getErpGoodsSpecId().intValue() == 0) {
                    //
                    return new ResultVo<>(EnumResultVo.DataError, "订单item找不到商品id信息");
                }

            } catch (Exception e) {
                return new ResultVo<>(EnumResultVo.NotFound, "订单不存在");// 订单不存在return 404;
            }

            /*********** 查询仓库系统退货订单 是否存在 *************/
            var erpOrderReturn = jdbcTemplate.query(
                    "SELECT * FROM " + Tables.ErpOrderReturn + " WHERE order_num=? AND shopId=?",
                    new BeanPropertyRowMapper<>(ErpOrderReturnEntity.class), refundOrder.getOrderId(),
                    order.getShopId());
            if (erpOrderReturn != null && erpOrderReturn.size() > 0) {
                // 退货单已经存在
                return new ResultVo<>(EnumResultVo.Fail, "仓库已经存在该退货单");
            }

            /*********** 插入仓库系统退款订单 erp_order_return erp_order_return_item *************/
            // 插入仓库退货订单 erp_order_return
            String returnOrder = "INSERT INTO " + Tables.ErpOrderReturn +
                    " (order_num,orderTime,createTime,contactPerson,mobile,address,source,status,logisticsCompany,logisticsCompanyCode,logisticsCode,source_order_num,shopId)"
                    +
                    " VALUE (?,?,?,?,?,?,?,?,?,?,?,?,?)";
            KeyHolder keyHolder = new GeneratedKeyHolder();

            DcDouyinOrdersEntity finalOrder = order;
            jdbcTemplate.update(new PreparedStatementCreator() {
                @Override
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    PreparedStatement ps = connection.prepareStatement(returnOrder, Statement.RETURN_GENERATED_KEYS);
                    ps.setString(1, String.valueOf(refundOrder.getOrderId()));
                    ps.setObject(2, refundOrder.getApplyTime());
                    ps.setLong(3, System.currentTimeMillis() / 1000);
                    ps.setString(4, finalOrder.getPostReceiver());
                    ps.setString(5, finalOrder.getPostTel());
                    ps.setString(6, finalOrder.getPostAddr());
                    ps.setString(7, ErpOrderSourceEnum.DOUYIN.getIndex());
                    ps.setInt(8, EnumOrderReturnStatus.WaitReceive.getIndex());
                    ps.setString(9, logisticsCompany);
                    ps.setString(10, "");
                    ps.setString(11, logisticsCode);
                    ps.setString(12, refundOrder.getOrderId());
                    ps.setInt(13, 8);
                    return ps;
                }
            }, keyHolder);
            Long returnOrderId = keyHolder.getKey().longValue();

            // 插入仓库退货订单明细 erp_order_return_item
            String returnOrderItemSQL = "INSERT INTO " + Tables.ErpOrderReturnItem
                    + " (orderId,goodsId,skuId,skuNumber,quantity,receiveType,createTime,status,inQuantity,badQuantity,refundAmount) VALUE (?,?,?,?,?,?,?,?,?,?,?)";

            // 更新退货商品信息
            jdbcTemplate.update(returnOrderItemSQL, returnOrderId, orderItem.getErpGoodsId(),
                    orderItem.getErpGoodsSpecId(), orderItem.getSpecCode(), refundOrder.getComboNum(), 0,
                    System.currentTimeMillis() / 1000, 0, 0, 0, refundOrder.getTotalAmount());

            /*********** 更新退款订单状态 ***********/
            jdbcTemplate.update(
                    "UPDATE dc_douyin_orders_refund SET logistics_company=?,logistics_code=?,auditStatus=1,stockStatus=3 WHERE id=?",
                    logisticsCompany, logisticsCode, id);

        } catch (Exception ex) {
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return new ResultVo<>(EnumResultVo.Fail, "同意退货处理失败");
        }
        return new ResultVo<>(EnumResultVo.SUCCESS, "成功");
    }

    public void updOrderLogisticsCode(Long orderId, String logisticsId, String logisticsCode, Integer print) {
        jdbcTemplate.update(
                "update dc_douyin_orders set logistics_code=?,printStatus=?,logistics_id=? where order_id=? ",
                logisticsCode, print, logisticsId, orderId);
    }

    public void updOrderPrint(Long orderId, String result, Integer orderStatus, Integer print) {
        jdbcTemplate.update("update dc_douyin_orders set order_status=?,printStatus=?,result=? where order_id=? ",
                orderStatus, print, result, orderId);
        if (print == 2) {
            jdbcTemplate.update("update dc_douyin_orders set printDate=? where order_id=? ", DateUtil.getCurrentDate(),
                    orderId);
        }

    }

    /**
     * 抖店订单取消
     * 
     * @param orderId
     * @param status
     * @param cancelReason
     */
    @Transactional
    public void dyOrderCancelNotify(Long orderId, Integer status, String cancelReason) {
        try {
            /*** 查询订单 ***/
            // var order = jdbcTemplate.queryForObject("SELECT id,order_id,order_status FROM
            // dc_douyin_orders WHERE order_id=?",new
            // BeanPropertyRowMapper<>(DcDouyinOrdersEntity.class),orderId);
            // if(order.getOrderStatus().intValue() == 3){
            // //状态是已发货的，
            // }
            String sql = "UPDATE dc_douyin_orders SET order_status=?,cancel_reason=? WHERE order_id=?";
            jdbcTemplate.update(sql, status, cancelReason, orderId);
            log.info("抖店订单取消擦操作SUCCESS" + orderId);
        } catch (Exception e) {
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            log.info("抖店订单取消擦操作：系统异常：" + e.getMessage());
        }
    }

    public ResultVo<List<DcDouyinOrdersItemsEntity>> getPrintOrderList(Long orderId, Integer isHebing, Integer print) {
        List<DcDouyinOrdersItemsEntity> lists = new ArrayList<>();
        StringBuilder SQL = new StringBuilder(
                "select item.*,o.order_id,o.province,o.city,o.town,o.order_status,o.encrypt_post_tel,o.encrypt_post_receiver,o.encryptDetail,o.phoneKey,o.addressKey,o.logistics_code,o.logistics_company,o.result,o.logistics_id  ");
        SQL.append(
                " from dc_douyin_orders_items item left join dc_douyin_orders o on item.dc_douyin_orders_id=o.id where o.order_status=2 and item.item_status <> 4 ");
        SQL.append(" and o.printStatus=").append(print);

        var orders = jdbcTemplate.query(SQL.append(" and o.order_id=? ").toString(),
                new BeanPropertyRowMapper<>(DcDouyinOrdersItemsEntity.class), orderId);
        if (orders.size() == 0)
            return new ResultVo<>(EnumResultVo.DataError, orderId + "不存在");

        var order = orders.get(0);

        if (isHebing == 0)
            lists.add(order);

        if (isHebing == 1) {
            StringBuilder SQLHB = new StringBuilder(
                    "select item.*,o.order_id,o.province,o.city,o.town,o.order_status,o.encrypt_post_tel,o.encrypt_post_receiver,o.encryptDetail,o.phoneKey,o.addressKey,o.logistics_code,o.logistics_company,o.result,o.logistics_id  ");
            SQLHB.append(
                    " from dc_douyin_orders_items item left join dc_douyin_orders o on item.dc_douyin_orders_id=o.id where o.order_status=2 AND o.printStatus=0 ");
            SQLHB.append(" AND LENGTH(phoneKey)>0 and o.phoneKey=? and o.addressKey=? limit 3 ");
            lists = jdbcTemplate.query(SQLHB.toString(), new BeanPropertyRowMapper<>(DcDouyinOrdersItemsEntity.class),
                    order.getPhoneKey(), order.getAddressKey());
        }

        // var order = getOderDetailByOrderId(orderId);

        boolean isNotCheck = false;

        List<Long> orderIdList = new ArrayList<>();
        lists.forEach(o -> orderIdList.add(Long.parseLong(o.getOrderId())));
        // orderIdList.add(orderId);
        /*
         * for (var item : order.getItems()) {
         * //是孤品则不需要检查库存信息
         *//*
            * if(order.getItems().size()==1 && item.getCode().contains("GP00")){
            * isNotCheck=true;
            * continue;
            * }
            *//*
               * if(StringUtils.isEmpty(item.getCode())){
               * jdbcTemplate.
               * update("UPDATE dc_douyin_orders SET result=?,printStatus=0 WHERE id=?"
               * ,"订单商品SKU不存在",item.getOrderId());
               * return new ResultVo<>(EnumResultVo.DataError, "订单商品没有SKU信息");
               * }
               * 
               * ErpGoodsSpecEntity erpGoodsSpec = null;
               * try {
               * erpGoodsSpec = jdbcTemplate.
               * queryForObject("select spec.*,eg.number as goodsNumber from erp_goods_spec spec LEFT JOIN erp_goods eg ON spec.goodsId=eg.id where spec.specNumber=? LIMIT 1"
               * , new BeanPropertyRowMapper<>(ErpGoodsSpecEntity.class), item.getCode());
               * } catch (Exception E) {
               * jdbcTemplate.
               * update("UPDATE dc_douyin_orders SET result=?,printStatus=0 WHERE id=?","SKU【"
               * + item.getCode() + "】不存在",item.getOrderId());
               * return new ResultVo<>(EnumResultVo.DataError, "SKU【" + item.getCode() +
               * "】不存在");
               * }
               * 
               * Integer stockCount = jdbcTemplate.
               * queryForObject("select count(0) from erp_goods_stock_info where specId=? and isDelete=0 limit 1"
               * ,Integer.class,erpGoodsSpec.getId());
               * if(stockCount.intValue()==0){
               * jdbcTemplate.
               * update("UPDATE dc_douyin_orders SET result=?,printStatus=0 WHERE id=?"
               * ,"仓库查询不到该商品库存",item.getOrderId());
               * return new ResultVo<>(EnumResultVo.DataError, "仓库查询不到该SKU【" + item.getCode()
               * + "】库存");
               * }
               * String
               * updItemSQL="update dc_douyin_orders_items set goodsNumber=?,spec_desc=?,erpGoodsId=?,erpGoodsSpecId=? where id=?"
               * ;
               * jdbcTemplate.update(updItemSQL,erpGoodsSpec.getGoodsNumber(),erpGoodsSpec.
               * getColorValue()+erpGoodsSpec.getSizeValue(),erpGoodsSpec.getGoodsId(),
               * erpGoodsSpec.getId(),item.getId());
               * }
               */

        // if(isNotCheck) return new ResultVo<>(EnumResultVo.DataError,"订单是孤品");
        try {
            StringBuilder qtySql = new StringBuilder(
                    "SELECT oi.order_id,SUM(oi.combo_num) AS quantity,oi.code as skuNumber ");
            qtySql.append(
                    ", IFNULL((SELECT IFNULL(SUM(currentQty),0) FROM erp_goods_stock_info WHERE specId=oi.erpGoodsSpecId AND isDelete=0),0) AS currentQty ");
            qtySql.append(
                    ", IFNULL((SELECT SUM(i.combo_num)  from dc_douyin_orders_items i LEFT JOIN dc_douyin_orders d ON i.dc_douyin_orders_id=d.id WHERE d.printStatus=1 and i.erpGoodsSpecId=oi.erpGoodsSpecId),0) as printQty ");
            qtySql.append(
                    ", IFNULL((SELECT SUM(it1.quantity) FROM erp_order_item AS it1 WHERE it1.skuId=oi.erpGoodsSpecId AND  it1.`status` <= 1 ),0) AS pickingQty ");
            qtySql.append(
                    " FROM dc_douyin_orders_items oi WHERE oi.parent_order_id IN (:ids) AND oi.item_status <> 4 GROUP BY oi.erpGoodsSpecId ");

            Map<String, Object> args = new HashMap<>();
            args.put("ids", orderIdList);
            NamedParameterJdbcTemplate givenParamJdbcTemp = new NamedParameterJdbcTemplate(jdbcTemplate);

            List<ErpOrderItemEntity> itemList = givenParamJdbcTemp.query(qtySql.toString(), args,
                    new BeanPropertyRowMapper<>(ErpOrderItemEntity.class));
            /************ 判断订单商品库存 ************/
            for (var item : itemList) {
                // 查询库存
                long keyongQty = item.getCurrentQty() - item.getPickingQty() - item.getPrintQty();
                if (keyongQty < item.getQuantity().longValue()) {
                    jdbcTemplate.update("UPDATE dc_douyin_orders SET result=?,printStatus=4 WHERE order_id=?",
                            item.getSkuNumber() + "库存不足", item.getOrderId());
                    return new ResultVo<>(EnumResultVo.DataError, item.getSkuNumber() + "库存不足" + item.getOrderId());
                }
            }
        } catch (Exception e) {
            return new ResultVo<>(EnumResultVo.DataError, orderId + "查询订单库存异常");
        }
        return new ResultVo<>(EnumResultVo.SUCCESS, "成功", lists);
    }

    @Transactional(rollbackFor = Exception.class)
    public ResultVo<Integer> cancelOrderPrint(Long orderId) {
        DcDouyinOrdersEntity order = null;
        try {
            // 查询关联的订单
            order = jdbcTemplate.queryForObject("SELECT * FROM dc_douyin_orders WHERE order_id=?",
                    new BeanPropertyRowMapper<>(DcDouyinOrdersEntity.class), orderId);
        } catch (Exception e) {
            return new ResultVo<>(EnumResultVo.NotFound, "订单不存在");// 订单不存在return 404;
        }
        try {
            // 查询仓库订单，未出库删除。
            var erpOrders = jdbcTemplate.query("select * from erp_order where order_num=?",
                    new BeanPropertyRowMapper<>(ErpOrderEntity.class), order.getOrderId());
            if (erpOrders.size() == 0) {
                jdbcTemplate.update("update dc_douyin_orders set printStatus=3 where order_id=?", orderId);
                return new ResultVo<>(EnumResultVo.SUCCESS, "成功");
            }
            if (erpOrders.size() > 0) {
                // 未生成拣货单 删除erp订单
                if (erpOrders.get(0).getStatus() <= 0) {
                    jdbcTemplate.update("delete from erp_order where id=?", erpOrders.get(0).getId());
                    jdbcTemplate.update("delete from erp_order_item where orderId=?", erpOrders.get(0).getId());
                    jdbcTemplate.update("update dc_douyin_orders set printStatus=3 where order_id=?", orderId);
                    return new ResultVo<>(EnumResultVo.SUCCESS, "成功");
                }
                // 已出库生成退货单入库
                Integer orderRurunCount = jdbcTemplate.queryForObject(
                        "select count(0) from erp_order_return where source_order_num=?", Integer.class,
                        order.getOrderId());
                if (orderRurunCount.intValue() > 0) {
                    jdbcTemplate.update("update dc_douyin_orders set printStatus=3 where order_id=?", orderId);
                    return new ResultVo<>(EnumResultVo.SUCCESS, "成功");
                }
                // 已出库，新增原单退货入库
                String returnOrder = "INSERT INTO " + Tables.ErpOrderReturn +
                        " (order_num,orderTime,createTime,contactPerson,mobile,address,source,status,logisticsCompany,logisticsCompanyCode,logisticsCode,source_order_num,shopId)"
                        +
                        " VALUE (?,?,?,?,?,?,?,?,?,?,?,?,?)";
                KeyHolder keyHolder = new GeneratedKeyHolder();

                DcDouyinOrdersEntity finalOrder = order;
                jdbcTemplate.update(new PreparedStatementCreator() {
                    @Override
                    public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                        PreparedStatement ps = connection.prepareStatement(returnOrder,
                                Statement.RETURN_GENERATED_KEYS);
                        ps.setString(1, String.valueOf(finalOrder.getOrderId()));
                        ps.setObject(2, finalOrder.getCreateTime());
                        ps.setLong(3, System.currentTimeMillis() / 1000);
                        ps.setString(4, "");
                        ps.setString(5, "");
                        ps.setString(6, "");
                        ps.setString(7, ErpOrderSourceEnum.PDD.getIndex());
                        ps.setInt(8, EnumOrderReturnStatus.WaitReceive.getIndex());
                        ps.setString(9, finalOrder.getLogisticsCompany());
                        ps.setString(10, "");
                        ps.setString(11, finalOrder.getLogisticsCode());
                        ps.setString(12, finalOrder.getOrderId());
                        ps.setInt(13, 8);
                        return ps;
                    }
                }, keyHolder);
                Long returnOrderId = keyHolder.getKey().longValue();

                // 插入仓库退货订单明细 erp_order_return_item
                String returnOrderItemSQL = "INSERT INTO " + Tables.ErpOrderReturnItem
                        + " (orderId,goodsId,skuId,skuNumber,quantity,receiveType,createTime,status,inQuantity,badQuantity,refundAmount,stockInfoItemId) VALUE (?,?,?,?,?,?,?,?,?,?,?,?)";

                var items = jdbcTemplate.query("select * from dc_douyin_orders_items where dc_douyin_orders_id=?",
                        new BeanPropertyRowMapper<>(DcDouyinOrdersItemsEntity.class), finalOrder.getId());
                for (var item : items) {
                    Long stockInfoItemId = jdbcTemplate.queryForObject(
                            "SELECT IFNULL((SELECT stockInfoItemId  FROM erp_order_item i LEFT JOIN erp_order e ON i.orderId=e.id WHERE e.order_num=? AND  i.skuId=? LIMIT 1),0) id",
                            Long.class, order.getOrderId(), item.getErpGoodsSpecId());
                    jdbcTemplate.update(returnOrderItemSQL, returnOrderId, item.getErpGoodsId(),
                            item.getErpGoodsSpecId(), item.getSpecCode(), item.getComboNum(), 0,
                            System.currentTimeMillis() / 1000, 0, 0, 0, item.getPrice(), stockInfoItemId);
                }
                jdbcTemplate.update("update dc_douyin_orders set printStatus=3 where order_id=?", orderId);
                return new ResultVo<>(EnumResultVo.SUCCESS, "成功(" + finalOrder.getOrderId() + ")仓库需要手动确认收货");
            }
            return new ResultVo<>(EnumResultVo.SUCCESS, "成功");
        } catch (Exception ex) {
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return new ResultVo<>(EnumResultVo.Fail, "系统异常");
        }

    }

    /**
     * 手动发货
     * 
     * @param id
     * @param company
     * @param code
     * @return
     */
    public ResultVo<Integer> orderHandSend(Long id, String company, String code) {
        DcDouyinOrdersEntity order = null;
        try {
            // 查询关联的订单
            order = jdbcTemplate.queryForObject("SELECT * FROM dc_douyin_orders WHERE id=?",
                    new BeanPropertyRowMapper<>(DcDouyinOrdersEntity.class), id);
        } catch (Exception e) {
            return new ResultVo<>(EnumResultVo.NotFound, "订单不存在");// 订单不存在return 404;
        }
        if (order.getOrderStatus() != 2) {
            return new ResultVo<>(EnumResultVo.DataError, "订单状态错误" + order.getOrderStatus());
        }
        String sql = "UPDATE dc_douyin_orders SET logistics_time=?,logistics_company=?,logistics_code=?,send_status=4,send_time=?,order_status=3,order_status_str='已发货' WHERE id=? ";
        jdbcTemplate.update(sql, new Date(), company, code, new Date(), id);
        return new ResultVo<>(EnumResultVo.SUCCESS, "SUCCESS");
    }

    /**
     * 订单发货
     *
     * @param order
     * @return
     */
    @Transactional(rollbackFor = Exception.class)
    public ResultVo<Integer> orderSend(DcDouyinOrdersListVo order) {
        try {
            Integer erpOrderCount = jdbcTemplate.queryForObject("SELECT count(0) FROM erp_order WHERE order_num=? ",
                    Integer.class, order.getOrderId());

            if (erpOrderCount > 0) {
                jdbcTemplate.update("update dc_douyin_orders set order_status=?,auditStatus=1 where order_id=?",
                        EnumDouYinOrderStatus.SEND_GOODS.getThirdIndex(), order.getOrderId());
                return new ResultVo<>(EnumResultVo.SUCCESS, "成功");
            }
            // 3.确认到仓库
            /********** 1.1、开始插入仓库系统订单表erp_order **********/
            StringBuilder orderInsertSQL = new StringBuilder();
            orderInsertSQL.append("INSERT INTO ").append(Tables.ErpOrder);
            orderInsertSQL.append(" SET ");
            orderInsertSQL.append(" order_num=?,");
            orderInsertSQL.append(" totalAmount=?,");
            orderInsertSQL.append(" shippingFee=?,");
            orderInsertSQL.append(" orderTime=?,");
            orderInsertSQL.append(" createTime=?,");
            orderInsertSQL.append(" modifyTime=?,");
            orderInsertSQL.append(" confirmedTime=?,");
            orderInsertSQL.append(" remark=?,");
            orderInsertSQL.append(" buyerFeedback=?,");
            orderInsertSQL.append(" sellerMemo=?,");
            orderInsertSQL.append(" contactPerson=?,");
            orderInsertSQL.append(" mobile=?,");
            orderInsertSQL.append(" province=?,");
            orderInsertSQL.append(" city=?,");
            orderInsertSQL.append(" area=?,");
            orderInsertSQL.append(" areaCode=?,");
            orderInsertSQL.append(" town=?,");
            orderInsertSQL.append(" townCode=?,");
            orderInsertSQL.append(" address=?,");
            orderInsertSQL.append(" source=?,");
            orderInsertSQL.append(" status=?,");
            orderInsertSQL.append(" shopId=?,");
            orderInsertSQL.append(" logisticsCompany=?, ");
            orderInsertSQL.append(" logisticsCode=?, ");
            orderInsertSQL.append(" sale_type=? ");

            KeyHolder keyHolder = new GeneratedKeyHolder();
            jdbcTemplate.update(new PreparedStatementCreator() {
                @Override
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    PreparedStatement ps = connection.prepareStatement(orderInsertSQL.toString(),
                            Statement.RETURN_GENERATED_KEYS);
                    ps.setString(1, order.getOrderId());
                    ps.setBigDecimal(2, BigDecimal.valueOf(order.getOrderTotalAmount()));
                    ps.setBigDecimal(3, new BigDecimal(0));
                    ps.setLong(4, 0l);
                    ps.setLong(5, System.currentTimeMillis() / 1000);
                    ps.setLong(6, System.currentTimeMillis() / 1000);
                    ps.setLong(7, System.currentTimeMillis() / 1000);
                    ps.setString(8, order.getBuyerWords());
                    ps.setString(9, "");
                    ps.setString(10, "");
                    ps.setString(11, "");
                    ps.setString(12, "");
                    ps.setString(13, order.getProvince());
                    ps.setString(14, order.getCity());
                    ps.setString(15, order.getTown());
                    ps.setString(16, "");
                    ps.setString(17, "");
                    ps.setString(18, "");
                    ps.setString(19, "");
                    ps.setString(20, ErpOrderSourceEnum.PDD.getIndex());
                    ps.setInt(21, EnumErpOrderSendStatus.WaitOut.getIndex());
                    ps.setInt(22, 8);
                    ps.setString(23, order.getLogisticsCompany());
                    ps.setString(24, order.getLogisticsCode());
                    ps.setInt(25, 1);
                    return ps;
                }
            }, keyHolder);

            Long erpOrderId = keyHolder.getKey().longValue();

            /********** 1.2、开始插入仓库系统订单明细表erp_order_item **********/
            StringBuilder orderItemInsertSQL = new StringBuilder();
            orderItemInsertSQL.append("INSERT INTO ").append(Tables.ErpOrderItem);
            orderItemInsertSQL.append(" SET ");
            orderItemInsertSQL.append(" orderId=?,");
            orderItemInsertSQL.append(" aliSubItemID=?,");
            orderItemInsertSQL.append(" productMallName=?,");
            orderItemInsertSQL.append(" productId=?,");
            orderItemInsertSQL.append(" productNumber=?,");
            orderItemInsertSQL.append(" skuNumber=?,");
            orderItemInsertSQL.append(" skuId=?,");
            orderItemInsertSQL.append(" productImgUrl=?,");
            orderItemInsertSQL.append(" quantity=?,");
            orderItemInsertSQL.append(" status=?,");
            orderItemInsertSQL.append(" itemAmount=?");

            for (var item : order.getItems()) {
                jdbcTemplate.update(orderItemInsertSQL.toString(),
                        erpOrderId,
                        item.getId(),
                        item.getProductName(),
                        item.getErpGoodsId(), // 这个字段的值已经变成了仓库系统的goodsId
                        item.getGoodsNumber(),
                        item.getSpecCode(),
                        item.getErpGoodsSpecId(),
                        item.getProductPic(),
                        item.getComboNum().longValue(),
                        EnumErpOrderSendStatus.WaitOut.getIndex(),
                        item.getTotalAmount());
            }
            jdbcTemplate.update("update dc_douyin_orders set order_status=?,auditStatus=1 where order_id=?",
                    EnumDouYinOrderStatus.SEND_GOODS.getThirdIndex(), order.getOrderId());
            return new ResultVo<>(EnumResultVo.SUCCESS, "成功");
        } catch (Exception e) {
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return new ResultVo<>(EnumResultVo.Fail, order.getOrderId() + "发货确认到仓库系统异常，" + e.getMessage());
        }
    }

    public void updRefundOrderAuditStatus(Long orderId, Integer auditStatus) {
        jdbcTemplate.update("update dc_douyin_orders_refund set auditStatus=? where id=? ", auditStatus, orderId);
    }

    /**
     * 订单批量修改sku
     *
     * @param orderIds
     * @param oldSku
     * @param newSku
     * @return
     */
    public ResultVo<Integer> ordersUpdSku(ArrayList orderIds, String oldSku, String newSku) {
        String erpGoodSpecSQL = "select spec.id,spec.goodsId productId,spec.color_value,spec.size_value,eg.number as productNumber, IFNULL((SELECT IFNULL(SUM(currentQty),0) FROM erp_goods_stock_info WHERE specId=spec.id AND isDelete=0),0) AS currentQty,IFNULL((SELECT SUM(i.combo_num)  from dc_douyin_orders_items i LEFT JOIN dc_douyin_orders d ON i.dc_douyin_orders_id=d.id WHERE d.printStatus=1 and i.erpGoodsSpecId=spec.id),0) as printQty,IFNULL((SELECT SUM(it1.quantity) FROM erp_order_item AS it1 WHERE it1.skuId=spec.id AND  it1.`status` <= 1 ),0) AS pickingQty from erp_goods_spec spec LEFT JOIN erp_goods eg ON spec.goodsId=eg.id where spec.specNumber=? LIMIT 1";
        var skus = jdbcTemplate.query(erpGoodSpecSQL, new BeanPropertyRowMapper<>(ErpOrderItemEntity.class),
                newSku.trim());
        if (skus.size() == 0)
            return new ResultVo<>(EnumResultVo.Fail, "sku不存在");
        var sku = skus.get(0);
        long keyongQty = sku.getCurrentQty() - sku.getPickingQty() - sku.getPrintQty();
        if (keyongQty <= 0)
            return new ResultVo<>(EnumResultVo.Fail, newSku + "库存为" + keyongQty);
        Integer count = 0;
        for (var orderSn : orderIds) {
            String orderSn_ = String.valueOf(orderSn);
            Long orderId = Long.parseLong(orderSn_);
            getOderDetailByOrderId(orderId);
            StringBuilder itemSql = new StringBuilder(
                    "select item.* from  dc_douyin_orders_items item where parent_order_id=? and item.code=?");
            var orderItem = jdbcTemplate.query(itemSql.toString(),
                    new BeanPropertyRowMapper<>(DcDouyinOrdersItemsEntity.class), orderId, oldSku);
            for (var item : orderItem) {
                count = count + item.getComboNum();
                jdbcTemplate.update(
                        "update dc_douyin_orders_items set erpGoodsId=?,erpGoodsSpecId=?,goodsNumber=?,code=? where id=? ",
                        sku.getProductId(), sku.getId(), sku.getProductNumber(), newSku.trim(), item.getId());
            }
            if (keyongQty < count)
                continue;
        }
        return new ResultVo<>(EnumResultVo.SUCCESS, "修改成功");
    }

    public ResultVo<Integer> ordersUpdSkuById(Long id, String newSku) {
        String erpGoodSpecSQL = "select spec.id,spec.goodsId productId,spec.color_value,spec.size_value,eg.number as productNumber, IFNULL((SELECT IFNULL(SUM(currentQty),0) FROM erp_goods_stock_info WHERE specId=spec.id AND isDelete=0),0) AS currentQty,IFNULL((SELECT SUM(i.combo_num)  from dc_douyin_orders_items i LEFT JOIN dc_douyin_orders d ON i.dc_douyin_orders_id=d.id WHERE d.printStatus=1 and i.erpGoodsSpecId=spec.id),0) as printQty,IFNULL((SELECT SUM(it1.quantity) FROM erp_order_item AS it1 WHERE it1.skuId=spec.id AND  it1.`status` <= 1 ),0) AS pickingQty from erp_goods_spec spec LEFT JOIN erp_goods eg ON spec.goodsId=eg.id where spec.specNumber=? LIMIT 1";
        var skus = jdbcTemplate.query(erpGoodSpecSQL, new BeanPropertyRowMapper<>(ErpOrderItemEntity.class),
                newSku.trim());
        if (skus.size() == 0)
            return new ResultVo<>(EnumResultVo.Fail, "sku不存在");
        var sku = skus.get(0);
        // long keyongQty = sku.getCurrentQty() - sku.getPickingQty() -
        // sku.getPrintQty();
        // if (keyongQty <= 0) return new ResultVo<>(EnumResultVo.Fail, newSku + "库存为" +
        // keyongQty);
        jdbcTemplate.update(
                "update dc_douyin_orders_items set erpGoodsId=?,erpGoodsSpecId=?,goodsNumber=?,code=?,spec_desc=? where id=? ",
                sku.getProductId(), sku.getId(), sku.getProductNumber(), newSku.trim(),
                sku.getColorValue() + sku.getSizeValue(), id);
        return new ResultVo<>(EnumResultVo.SUCCESS, "修改成功");
    }

    public void zhuBoOrderSettle(List<Long> ids, Integer isSettle) {
        for (var id : ids) {
            jdbcTemplate.update("update dc_douyin_orders set isSettle=? where order_id=?", isSettle, id);
            // jdbcTemplate.update("update erp_order set settleSign=? where
            // order_num=?",202111,String.valueOf(id));
        }
    }

    public List<DouyinOrderStatis> douyinOrderStatis(String startTime, String endTime) {
        jdbcTemplate.update(
                "UPDATE dc_douyin_orders o LEFT JOIN dc_douyin_orders_items i ON i.dc_douyin_orders_id=o.id SET o.author_id=i.author_id WHERE i.author_id>0;");

        List<DouyinOrderStatis> statis = new ArrayList<>();

        var zbList = jdbcTemplate.query("select platform_id zbId,name zbName from zhibo_account ",
                new BeanPropertyRowMapper<>(DouyinOrderStatis.class));
        for (var zb : zbList) {
            StringBuilder sb = new StringBuilder(
                    "SELECT COUNT(o.id) orderCount,o.author_id,SUM(order_total_amount) orderAmount,SUM(i.combo_num) goodNum  from dc_douyin_orders  o LEFT JOIN dc_douyin_orders_items i on i.dc_douyin_orders_id=o.id \n"
                            +
                            "WHERE (o.order_status=1 OR o.order_status=2  OR o.order_status=3 OR o.order_status=5) ");
            List<Object> params = new ArrayList<>();

            if (!StringUtils.isEmpty(startTime)) {
                sb.append(" AND FROM_UNIXTIME(o.create_time,'%Y-%m-%d')>=?");
                params.add(startTime);
            }
            if (!StringUtils.isEmpty(endTime)) {
                sb.append("AND FROM_UNIXTIME(o.create_time,'%Y-%m-%d') <= ? ");
                params.add(endTime);
            }
            sb.append(" AND o.author_id = ? ");
            params.add(zb.getZbId());
            var obj = jdbcTemplate.queryForObject(sb.toString(), new BeanPropertyRowMapper<>(DouyinOrderStatis.class),
                    params.toArray(new Object[params.size()]));
            obj.setZbName(zb.getZbName());
            statis.add(obj);
        }
        return statis;
    }

    public void test(List<DataRow> datas) {
        try {
            for (var d : datas) {
                String sql = "insert  INTO zhibo_data set zhibo_date=?,zhanghao=?,ljgk=?,fs=?,dds=?,zfje=?,xdrs=?,yj=?,tuije=?,tuidds=?,tuirs=?,create_on=unix_timestamp(now())";
                jdbcTemplate.update(sql, d.getString("zhibo_date"), d.getString("zhanghao"), d.getString("ljgk"),
                        d.getString("fs"),
                        d.getString("dds"), d.getString("zfje"), d.getString("xdrs"), d.getString("yj"),
                        d.getString("tuije"),
                        d.getString("tuidds"), d.getString("tuirs"));
            }
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }

    }

    public Long getDyOrderTime(Integer shopId) {
        if (shopId == 8)
            shopId = 2148336;
        return jdbcTemplate.queryForObject(
                "select create_time from dc_douyin_orders WHERE shop_id = " + shopId + " order by id desc limit 1",
                Long.class);
    }

    /**
     * 获取直播间订单统计
     * 
     * @param pageIndex
     * @param pageSize
     * @param orderDate
     * @param shopId
     * @param authorId
     * @return
     */
    @Transactional
    public PagingResponse<DouyinOrderStatisticsEntity> getOrderStatisticsList(Integer pageIndex, Integer pageSize,
            String orderDate, Integer shopId, Long authorId) {
        StringBuilder sql = new StringBuilder("SELECT SQL_CALC_FOUND_ROWS ");
        sql.append("o.order_id,s.`name` as shopName,");
        sql.append(
                "oi.product_name,oi.product_pic,oi.combo_num as num,oi.`code` as specNum,oi.total_amount,oi.author_name,");
        sql.append(
                "o.order_status,o.create_time,g.salePrice,g.`number` AS productNum,IFNULL(g.cost_price,0) AS costPrice ");
        sql.append(" FROM dc_douyin_orders_items oi");
        sql.append(" LEFT JOIN dc_douyin_orders o ON o.id = oi.dc_douyin_orders_id");
        sql.append(" LEFT JOIN dc_shop s ON s.sellerUserId=o.shop_id");
        sql.append(" LEFT JOIN erp_goods g ON g.id = oi.erpGoodsId");
        sql.append(" WHERE (o.order_status = 2 OR o.order_status = 3 OR o.order_status = 5)");

        List<Object> params = new ArrayList<>();
        if (!StringUtils.isEmpty(orderDate)) {
            sql.append(" AND  FROM_UNIXTIME(O.create_time,'%Y-%m-%d') = ? ");
            params.add(orderDate);
        }
        if (!StringUtils.isEmpty(shopId)) {
            sql.append(" AND s.id = ? ");
            params.add(shopId);
        }
        if (!StringUtils.isEmpty(authorId)) {
            sql.append(" AND oi.author_id = ? ");
            params.add(authorId);
        }
        sql.append("ORDER BY o.create_time  LIMIT ?,?");
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);

        List<DouyinOrderStatisticsEntity> lists = jdbcTemplate.query(sql.toString(),
                new BeanPropertyRowMapper<>(DouyinOrderStatisticsEntity.class),
                params.toArray(new Object[params.size()]));
        int totalSize = getTotalSize();
        return new PagingResponse<>(pageIndex, pageSize, totalSize, lists);
    }

    public List<DouyinOrderStatisticsEntity> getOrderStatisticsListExport(String orderDate, Integer shopId,
            Long authorId) {
        StringBuilder sql = new StringBuilder("SELECT  ");
        sql.append("o.order_id,s.`name` as shopName,");
        sql.append(
                "oi.product_name,oi.product_pic,oi.combo_num as num,oi.`code` as specNum,oi.total_amount,oi.author_name,");
        sql.append(
                "o.order_status,o.create_time,g.salePrice,g.`number` AS productNum,IFNULL(g.cost_price,0) AS costPrice ");
        sql.append(" FROM dc_douyin_orders_items oi");
        sql.append(" LEFT JOIN dc_douyin_orders o ON o.id = oi.dc_douyin_orders_id");
        sql.append(" LEFT JOIN dc_shop s ON s.sellerUserId=o.shop_id");
        sql.append(" LEFT JOIN erp_goods g ON g.id = oi.erpGoodsId");
        sql.append(" WHERE (o.order_status = 2 OR o.order_status = 3 OR o.order_status = 5)");

        List<Object> params = new ArrayList<>();
        if (!StringUtils.isEmpty(orderDate)) {
            sql.append(" AND  FROM_UNIXTIME(O.create_time,'%Y-%m-%d') = ? ");
            params.add(orderDate);
        }
        if (!StringUtils.isEmpty(shopId)) {
            sql.append(" AND s.id = ? ");
            params.add(shopId);
        }
        if (!StringUtils.isEmpty(authorId)) {
            sql.append(" AND oi.author_id = ? ");
            params.add(authorId);
        }
        sql.append("ORDER BY o.create_time  ");

        List<DouyinOrderStatisticsEntity> lists = jdbcTemplate.query(sql.toString(),
                new BeanPropertyRowMapper<>(DouyinOrderStatisticsEntity.class),
                params.toArray(new Object[params.size()]));
        return lists;
    }

    /**
     * 导入订单
     * 
     * @param orderList
     * @param shopId
     * @return
     */
    @Transactional
    public ResultVo<String> importExcelOrder(List<DyOrderImportOrderVo> orderList, Integer shopId) {
        if (orderList == null || orderList.size() == 0)
            return new ResultVo<>(EnumResultVo.DataError, "参数错误：缺少orderList", "");

        /******* 插入数据 ********/
        int totalInsert = 0;// 新增数量
        int totalExist = 0;// 已存在数量
        int totalError = 0;// 错误数量
        for (var order : orderList) {
            Integer erpGoodsId = 0;
            Integer erpGoodsSpecId = 0;
            String erpGoodsNum = "";

            // 查询订单是否存在
            var oList = jdbcTemplate.query("SELECT * FROM dc_douyin_orders WHERE order_id=? ",
                    new BeanPropertyRowMapper<>(DcDouyinOrdersEntity.class), order.getOrderId());

            if (oList != null && oList.size() > 0) {
                // 已经存在，更新一下状态
                int status = oList.get(0).getOrderStatus();
                if (order.getOrderStatusStr().equals("备货中") || order.getOrderStatusStr().equals("待发货"))
                    status = 2;
                else if (order.getOrderStatusStr().equals("已发货"))
                    status = 3;
                else if (order.getOrderStatusStr().equals("已完成"))
                    status = 5;
                else if (order.getOrderStatusStr().equals("已关闭"))
                    status = 9;
                jdbcTemplate.update("update dc_douyin_orders set order_status=?,order_status_str=? WHERE order_id=?",
                        status, order.getOrderStatusStr(), order.getOrderId());
                log.info("订单存在，更新订单状态，原状态：" + oList.get(0).getOrderStatus() + "，新状态：" + status);
                // 查询子订单是否也存在
                var oiList = jdbcTemplate.query("SELECT * FROM dc_douyin_orders_items WHERE sub_order_id=? ",
                        new BeanPropertyRowMapper<>(DcDouyinOrdersEntity.class), order.getSubOrderId());
                if (oiList != null && oiList.size() > 0) {
                    // 子订单也存在
                    totalExist++;
                } else {
                    // 子订单不存在，添加子订单
                    try {
                        // 查询商品规格信息
                        String erpSpecSQL = "select egs.*,eg.`name` goodTitle,eg.number as goodsNumber,eg.id as goodsId,eg.image as goodsImage  from "
                                + Tables.ErpGoodsSpec + " egs LEFT JOIN " + Tables.ErpGoods
                                + " eg ON egs.goodsId=eg.id where egs.specNumber=?";
                        var goodsSpec = jdbcTemplate.queryForObject(erpSpecSQL,
                                new BeanPropertyRowMapper<>(ErpGoodsSpecEntity.class), order.getSpecCode().trim());
                        erpGoodsId = goodsSpec.getGoodsId();
                        erpGoodsSpecId = goodsSpec.getId();
                        erpGoodsNum = goodsSpec.getGoodsNumber();
                    } catch (Exception e) {
                        // 错误，sku编码不存在
                        totalError++;
                    }

                    String subSQL = "INSERT INTO dc_douyin_orders_items " +
                            "(dc_douyin_orders_id,order_id,sub_order_id,shop_id,product_id,product_name,spec_code,spec_desc,combo_num,post_amount,coupon_amount,total_amount,erpGoodsId,erpGoodsSpecId,goodsNumber,price)"
                            +
                            " VALUE (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
                    jdbcTemplate.update(subSQL, oList.get(0).getId(),
                            order.getOrderId(), order.getSubOrderId(), shopId, order.getProductId(),
                            order.getProductName(), order.getSpecCode(), order.getSpecDesc(), order.getQuantity(),
                            order.getPostAmount(), order.getCouponAmount(), order.getTotalAmount(), erpGoodsId,
                            erpGoodsSpecId, erpGoodsNum, order.getPrice());
                    log.info("订单【" + order.getOrderId() + "】存在，子订单不存在，新增子订单");
                }

            } else {
                try {
                    // 查询商品规格信息
                    String erpSpecSQL = "select egs.*,eg.`name` goodTitle,eg.number as goodsNumber,eg.id as goodsId,eg.image as goodsImage  from "
                            + Tables.ErpGoodsSpec + " egs LEFT JOIN " + Tables.ErpGoods
                            + " eg ON egs.goodsId=eg.id where egs.specNumber=?";
                    var goodsSpec = jdbcTemplate.queryForObject(erpSpecSQL,
                            new BeanPropertyRowMapper<>(ErpGoodsSpecEntity.class), order.getSpecCode().trim());
                    erpGoodsId = goodsSpec.getGoodsId();
                    erpGoodsSpecId = goodsSpec.getId();
                    erpGoodsNum = goodsSpec.getGoodsNumber();
                } catch (Exception e) {
                    // 错误，sku编码不存在
                    totalError++;
                    log.error("新增错误,sku编码不存在,订单id：" + order.getOrderId());
                }
                try {
                    int status = 1;
                    if (order.getOrderStatusStr().equals("备货中") || order.getOrderStatusStr().equals("待发货"))
                        status = 2;
                    else if (order.getOrderStatusStr().equals("已发货"))
                        status = 3;
                    else if (order.getOrderStatusStr().equals("已完成"))
                        status = 5;
                    else if (order.getOrderStatusStr().equals("已关闭"))
                        status = 9;
                        
                    final Integer state = status;
                    /************** 1、新增order **********************/
                    StringBuilder insertSQL = new StringBuilder();
                    insertSQL.append("INSERT INTO dc_douyin_orders ");
                    insertSQL.append(
                            " (order_id,shop_id,buyer_words,seller_words,order_status,order_status_str,create_time,exp_ship_time,");
                    insertSQL.append(
                            "pay_type_name,pay_time,post_amount,coupon_amount,order_total_amount,appSource,trafficeSource,");
                    insertSQL.append("province,city,town,street,author_id,author_name)");
                    insertSQL.append(" VALUE (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ");

                    KeyHolder keyHolder = new GeneratedKeyHolder();
                    jdbcTemplate.update(new PreparedStatementCreator() {
                        @Override
                        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                            PreparedStatement ps = connection.prepareStatement(insertSQL.toString(),
                                    Statement.RETURN_GENERATED_KEYS);
                            ps.setString(1, order.getOrderId());
                            ps.setInt(2, shopId);
                            ps.setString(3, order.getBuyerWords());
                            ps.setString(4, order.getSellerWords());
                            ps.setInt(5, state);
                            ps.setString(6, order.getOrderStatusStr());
                            ps.setString(7, order.getOrderCreateTime());
                            ps.setString(8, order.getExpShipTime());

                            ps.setString(9, order.getPayTypeName());
                            ps.setString(10, StringUtils.hasText(order.getOrderPayTime())?order.getOrderPayTime():null);
                            ps.setDouble(11, order.getPostAmount());
                            ps.setDouble(12, order.getCouponAmount());
                            ps.setDouble(13, order.getTotalAmount());
                            ps.setString(14, order.getAppSource());
                            ps.setString(15, order.getTrafficeSource());
                            ps.setString(16, order.getProvince());
                            ps.setString(17, order.getCity());
                            ps.setString(18, order.getTown());
                            ps.setString(19, order.getStreet());
                            ps.setLong(20, Long.parseLong(order.getAuthorId().trim()));
                            ps.setString(21, order.getAuthorName());

                            return ps;
                        }
                    }, keyHolder);

                    Long id = keyHolder.getKey().longValue();

                    /******************* 2、添加order_item **************************/
                    // 添加订单明细
                    String subSQL = "INSERT INTO dc_douyin_orders_items " +
                            "(dc_douyin_orders_id,order_id,sub_order_id,shop_id,product_id,product_name,spec_code,spec_desc,combo_num,post_amount,coupon_amount,total_amount,erpGoodsId,erpGoodsSpecId,goodsNumber,price)"
                            +
                            " VALUE (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
                    jdbcTemplate.update(subSQL, id,
                            order.getOrderId(), order.getSubOrderId(), shopId, order.getProductId(),
                            order.getProductName(), order.getSpecCode(), order.getSpecDesc(), order.getQuantity(),
                            order.getPostAmount(), order.getCouponAmount(), order.getTotalAmount(), erpGoodsId,
                            erpGoodsSpecId, erpGoodsNum, order.getPrice());

                    log.info("新增新订单" + order.getOrderId());
                    totalInsert++;// 新增成功

                } catch (Exception e) {
                    totalError++;
                    log.error("新增错误,系统异常："+e.getMessage() + order.getOrderId());
                    TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                    return new ResultVo<>(EnumResultVo.SystemException, "系统异常：" + e.getMessage(), "");
                }
            }
        }
        String msg = "新增成功：" + totalInsert + "，新增失败：" + totalError + "，已存在：" + totalExist;
        //// TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
        return new ResultVo<>(EnumResultVo.SUCCESS, "SUCCESS", msg);

    }

    public ResultVo<Integer> updOrderAddress(Long id, String receiverName, String receiverPhone,
            String receiverAddress) {
        String sql = "update dc_douyin_orders set post_receiver=?,post_tel=?,post_addr=? WHERE id=?";
        jdbcTemplate.update(sql, receiverName, receiverPhone, receiverAddress, id);
        return new ResultVo<>(EnumResultVo.SUCCESS, "SUCCESS");
    }

    /**
     * 手动退货
     * @param orderItemId
     * @param refundId
     * @return
     */
    @Transactional(rollbackFor = Exception.class)
    public ResultVo<Integer> handAddRefund(Long orderItemId,Long refundId) {
        Integer count = jdbcTemplate.queryForObject("select count(0) from dc_douyin_orders_refund where aftersale_id=?",Integer.class, refundId);
        if (count > 0) {
            return new ResultVo<>(EnumResultVo.DataExist, "退货单号"+refundId+"已存在");
        }

        try{

            var orderItem = jdbcTemplate.queryForObject("SELECT * FROM dc_douyin_orders_items WHERE id=?", new BeanPropertyRowMapper<>(DcDouyinOrdersItemsEntity.class),orderItemId);

            
            String sql ="INSERT INTO dc_douyin_orders_refund "+
            "(aftersale_id,aftersale_type,order_id,sub_order_id,shop_id,product_id,product_pic,product_name,goodsNumber,combo_id,spec_desc,spec_code,combo_num,order_amount,combo_amount,apply_time,refund_status,erpGoodsId,erpGoodsSpecId) "+
            "VALUE(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            jdbcTemplate.update(sql,refundId,0,orderItem.getOrderId(),orderItem.getSubOrderId(),orderItem.getShopId(),
            orderItem.getProductId(),orderItem.getProductPic(),orderItem.getProductName(),orderItem.getGoodsNumber(),orderItem.getComboId(),
            orderItem.getSpecDesc(),orderItem.getSpecCode(),orderItem.getComboNum(),
            orderItem.getTotalAmount(),orderItem.getTotalAmount(),DateUtil.getCurrentDateTime(),7,
            orderItem.getErpGoodsId(),orderItem.getErpGoodsSpecId());

            //更新子订单状态
            jdbcTemplate.update("update dc_douyin_orders_items set item_status=2 where id=?",orderItemId);
        
            return new ResultVo<>(EnumResultVo.SUCCESS, "成功");
        } catch (Exception e) {
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return new ResultVo<>(EnumResultVo.Fail, "异常：" + e.getMessage());
        }
        // return new ResultVo<>(EnumResultVo.SUCCESS,"成功");
    }


    public void editRefundLogisticsCode(Long id,String company,String code,String logisticsTime){
        String sql = "UPDATE dc_douyin_orders_refund SET logistics_company=?,logistics_code=?,logistics_time=?,refund_status=? WHERE id=? ";
        jdbcTemplate.update(sql,
        company,code,logisticsTime,EnumDouYinOrderRefundStatus.REFUND_STATUS_11.getIndex(),id);
    }
}
